package com.red.wood.module.ircs.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.red.wood.common.domain.Page;
import com.red.wood.common.util.CommonUtils;
import com.red.wood.module.ircs.dao.IrcsUserInfoDao;

@Repository
public class IrcsUserInfoDaoImpl implements IrcsUserInfoDao {

	private static final Log log = LogFactory.getLog(IrcsUserInfoDaoImpl.class);
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public Map<String, Object> getPageData(Map<String, Object> param) {
		String count = "0";
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		
		count = listCountStatistics(param);
		list = listPageDataStatistics(param);
		
		Map<String, Object> rs = new HashMap<String, Object>();
		rs.put("totalCount", count);
		rs.put("pageData", list);
		return rs;
	}
	
	@Override
	public List<Map<String, Object>> getDetailInfo(String userId,String ircsId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select t.unitName,t.userId,t.idType,t.idNo,t.ircsId,t.nature as unitNature,t.unitadd as address,t.officerName,t.officerTel,t.officerMobile,t.officerEmail,t.zipCode,t.registerTime from t_ircs_userinfo_result t where userId='"+userId+"' and ircsId = '"+ircsId+"'");
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	public List<Map<String, Object>> listPageDataStatistics(Map<String, Object> param){
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		Page page = (Page) param.get("page");
		int offset = (page.getPageNo() - 1) * page.getPageSize();
		StringBuffer sql = new StringBuffer();
		if(StringUtils.equals(userLevel,"1")) {
			sql = getSqlStatistics();
			sql.append(" and t.ircsProvince = '"+userProvince+"'");
			sql.append(" and t.userProvince = '"+userProvince+"'");
			sql.append(getQueryConf(param));
		}else if(StringUtils.equals(userLevel, "2")) {
			sql = getSqlStatistics();
			sql.append(" and t.ircsProvince = '"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(" order by t.unitName desc");
		}else {
			sql.append("select * from (");
			sql.append(getSqlStatistics());
			sql.append(" and t.ircsProvince = '"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(" union all ");
			sql.append(getSqlStatistics());
			sql.append(" and t.ircsProvince != '"+userProvince+"'");
			sql.append(" and t.userProvince = '"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(") a");
		}
		sql.append(" limit " + page.getPageSize() + " offset " + offset+ " ;");
		log.info(sql);
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	public List<Map<String, Object>> listPageData(Map<String, Object> param){
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		Page page = (Page) param.get("page");
		int offset = (page.getPageNo() - 1) * page.getPageSize();
		StringBuffer sql = new StringBuffer();
		if(StringUtils.equals(userLevel,"1")) {
			sql = getSql();
			sql.append(" and t.province = '"+userProvince+"'");
			sql.append(" and ircs.province = '"+userProvince+"'");
			sql.append(getQueryConf(param));
		}else if(StringUtils.equals(userLevel, "2")) {
			sql = getSql();
			sql.append(" and ircs.province = '"+userProvince+"'");
			sql.append(getQueryConf(param));
		}else {
			sql.append("select * from (");
			sql.append(getSql());
			sql.append(" and ircs.province = '"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(" union all ");
			sql.append(getSql());
			sql.append(" and ircs.province != '"+userProvince+"'");
			sql.append(" and t.province = '"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(") a");
		}
		sql.append(" limit " + page.getPageSize() + " offset " + offset+ " ;");
		log.info(sql);
		return jdbcTemplate.queryForList(sql.toString());
	}

	public StringBuffer getSqlStatistics() {
		StringBuffer sql = new StringBuffer();
		sql.append("select t.unitName,t.userId,t.ircsId,t.ircsName,t.domainNum,");
		sql.append("       case when t.nature = '1' then '互联网应用服务' when t.nature = '2' then '内部应用服务' when t.nature = '3' then '电信业务应用服务'  end as userNature,");
		sql.append("       case when t.nature = '1' then '互联网应用服务' when t.nature = '2' then '内部应用服务' when t.nature = '3' then '电信业务应用服务'  end as userNature,");
		sql.append(" from t_ircs_userinfo_statictis t");
		sql.append(" where 1 = 1");
		return sql;
	}
	
	public StringBuffer getSql() {
		StringBuffer sql = new StringBuffer();
		sql.append("select t.unitName,t.userId,t.ircsId,ircs.ircsName,");
		sql.append("       case when t.nature = '1' then '互联网应用服务' when t.nature = '2' then '内部应用服务' when t.nature = '3' then '电信业务应用服务'  end as userNature,");
		sql.append("       case when t.unitNature = '1' then '工商营业执照号码' when t.unitNature = '2' then '身份证' when t.unitNature = '3' then '组织机构代码证书' when t.unitNature = '4' then '事业法人证书' when t.unitNature = '5' then '军队代号' when t.unitNature = '6' then '社团法人证书' when t.unitNature = '7' then '护照' when t.unitNature = '8' then '军官证' when t.unitNature = '9' then '台胞证' when t.unitNature = '999' then '其他' end as unitNature,");
		sql.append("       (select count(*) from t_ircs_userinfo_serviceinfo_domain t1 where t.userId = t1.userId and t.ircsId = t1.ircsId) as domainNum");
		sql.append(" from t_ircs_userinfo_result t inner join t_ircs_ircsdata ircs");
		sql.append(" on t.ircsId = ircs.ircsId");
		sql.append(" where 1 = 1");
		return sql;
	}
	
	public String listCountStatistics(Map<String, Object> param) {
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		StringBuffer sql = new StringBuffer();
		if(StringUtils.equals(userLevel,"1")) {
			sql.append("select count(*) count from t_ircs_userinfo_statictis t where 1 = 1");
			sql.append(" and t.userProvince = '"+userProvince+"'");
			sql.append(" and t.ircsProvince = '"+userProvince+"'");
			sql.append(getQueryConf(param));
		}else if(StringUtils.equals(userLevel, "2")) {
			sql.append("select count(*) count from t_ircs_userinfo_statictis t where 1 = 1");
			sql.append(" and t.ircsProvince = '"+userProvince+"'");
			sql.append(getQueryConf(param));
		}else {
			sql.append("select count(*) count from (");
			sql.append(getSqlStatistics());
			sql.append(" and t.ircsProvince = '"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(" union all ");
			sql.append(getSqlStatistics());
			sql.append(" and t.ircsProvince != '"+userProvince+"'");
			sql.append(" and t.userProvince = '"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(") a");
		}
		Map<String, Object> map = jdbcTemplate.queryForList(sql.toString()).get(0);
		String count = map.get("count").toString();
		return count;
	}
	
	public String listCount(Map<String, Object> param) {
		String userProvince = CommonUtils.getLoginUserProvince(CommonUtils.valueOf(param.get("loginUser")));
		String userLevel = CommonUtils.valueOf(param.get("userLevel"));
		StringBuffer sql = new StringBuffer();
		if(StringUtils.equals(userLevel,"1")) {
			sql.append("select count(*) count from (");
			sql.append(getSql());
			sql.append(" and t.province = '"+userProvince+"'");
			sql.append(" and ircs.province = '"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(") a");
		}else if(StringUtils.equals(userLevel, "2")) {
			sql.append("select count(*) count from (");
			sql.append(getSql());
			sql.append(" and ircs.province = '"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(") a");
		}else {
			sql.append("select count(*) count from (");
			sql.append(getSql());
			sql.append(" and ircs.province = '"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(" union all ");
			sql.append(getSql());
			sql.append(" and ircs.province != '"+userProvince+"'");
			sql.append(" and t.province = '"+userProvince+"'");
			sql.append(getQueryConf(param));
			sql.append(") a");
		}
		Map<String, Object> map = jdbcTemplate.queryForList(sql.toString()).get(0);
		String count = map.get("count").toString();
		return count;
	}
	
	public StringBuffer getQueryConf(Map<String,Object> param) {
		String ircsId = CommonUtils.valueOf(param.get("ircsId"));
		String userName = CommonUtils.valueOf(param.get("userName"));
		String unitNature = CommonUtils.valueOf(param.get("unitNature"));
		StringBuffer queryConf = new StringBuffer();
		
		if(StringUtils.isNotBlank(ircsId)) {
			queryConf.append(" and t.ircsId='"+ircsId+"'");
		}
		if(!StringUtils.equals(unitNature, "all")) {
			queryConf.append(" and t.unitNature='"+unitNature+"'");
		}
		if(StringUtils.isNotBlank(userName)) {
			queryConf.append(" and t.unitName like '%"+userName+"%'");
		}
		return queryConf;
	}
}
